import java.sql.*;

import java.util.Arrays;
import java.util.HashMap;
import java.util.ArrayList;
import java.util.List;

public class DB {

	public DB(){	}

	public static void insertUser(String user, String pass) 
	{
		/*
			  try {
			  Class.forName("org.sqlite.JDBC");
			  } catch (ClassNotFoundException e) {
			  throw new DbException("Could not find JDBC driver", e);
			  }
		 */

		Connection connection = null;
		PreparedStatement stmt = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);

			stmt = connection.prepareStatement("INSERT INTO users (username,password,level,categoriesDone) values(?,?,?,?)");
			stmt.setString(1, user);
			stmt.setString(2, pass);
			stmt.setString(3, "1");
			stmt.setString(4, "");
			stmt.executeUpdate();
			
			stmt.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
			System.exit(0);
		}
	}

  private static boolean closeConn(Connection connection, boolean b)
  {
    try {
      if (connection != null)
        connection.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    return b;
  }
	public static boolean userExists(String user) 
	{

		Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);

			ResultSet rs = statement.executeQuery("SELECT user_id FROM users WHERE username = '"+user+"'");

			while(rs.next())
				return closeConn(connection, true);
			statement.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}	

		return false;	
	}

	public static boolean login(String user, String pass) 
	{

    Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);

			//debug should just get password, not *
			ResultSet rs = statement.executeQuery("SELECT * FROM users WHERE username = '"+user+"'");

			while(rs.next())
				if(rs.getString("password").equals(pass))
					return closeConn(connection, true);
			statement.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}	

		return false;	
	}

	/**
	 * 
	 * @param table table names corrispond with categories so if you want animals then the table name ought to be animals
	 * @param level what level of words you want from that category
	 * @return hash map of english to swahili words based on the table and level.
	 * @throws ClassNotFoundException
	 */
	public static HashMap<String, String> getWords(String table, String level) 
	{
		HashMap<String,String> words = new HashMap<String,String>();

		Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);

			ResultSet results = statement.executeQuery("SELECT * FROM '"+table+"' WHERE level = '"+level+"' OR level IS NULL");

			while(results.next())
				words.put( results.getString(results.findColumn("english")), 
						results.getString(results.findColumn("swahili")) );
			statement.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}

		return words;
	}

	/**
	 * 
	 * @return returns an arraylist of words and a list of the columns. the first column being the name and the second column being the highScore
	 */
	public static ArrayList< List<String> > getAllGames()
	{
		ArrayList< List<String> > gamesList = new ArrayList< List<String> >();

		Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);

			ResultSet games = statement.executeQuery("SELECT * FROM games");

			while(games.next())
			{
				gamesList.add(Arrays.asList( games.getString(games.findColumn("name")), 
						Integer.toString(games.getInt(games.findColumn("highScore")))) );
			}
			statement.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}

		return gamesList;

	}

	/**
	 * Will always return null categories as well as level categories
	 * @param level level of categories that one wants. '1' = level 1, '2' = level 2 and so on. 
	 * @return arraylist of categories and a list of columns. first column being name and second column being imgLocation
	 */
	public static ArrayList< List<String> > getCategories(String level)
	{
		ArrayList< List<String> > gamesList = new ArrayList< List<String> >();

		Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);

			ResultSet games = statement.executeQuery("SELECT * FROM categories where level like '%"+level+"%'");

			while(games.next())
			{
				gamesList.add(Arrays.asList( games.getString(games.findColumn("name")), 
						games.getString(games.findColumn("imgLocation")),
						games.getString("id")) );
			}
			statement.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}

		return gamesList;

	}

	public static String getLevel(String user)
	{
		String level = "0";	
		Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);

			//ResultSet categories = statement.executeQuery("SELECT MAX(level) AS level FROM categories");
			ResultSet categories = statement.executeQuery("SELECT level FROM users WHERE username = '"+user+"'");
			while(categories.next())
			{
				level = categories.getString("level");
			}
			statement.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}

		if(level == null)
			return "0";
		return level;
	}

	public static boolean addDoneCategory(String user, String category, String level)
	{
		Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			Statement statement2 = connection.createStatement();
			
			ResultSet results = statement.executeQuery("SELECT level FROM users WHERE username = '"+user+"'");
			if(results.next())
			{
				//if user isn't on their highest level then don't count the categories as done 
				if(results.getString(1).compareTo(level) != 0)
					return closeConn(connection, false);
			} else return closeConn(connection, false);


			ResultSet cateId = statement2.executeQuery("SELECT id FROM categories WHERE name = '"+category+"'");
			ResultSet userCate = statement.executeQuery("SELECT categoriesDone FROM users WHERE username = '"+user+"' ");

			//if category has been completed before, don't duplicate the record
			if(Arrays.asList(userCate.getString(1).split(",")).contains(cateId.getString(1)))
        return closeConn(connection, false);

			String categoryDone = cateId.getString(1);
			statement.executeUpdate("update users set categoriesDone = categoriesDone || '"+categoryDone+",' where username = '"+user+"'");

			statement.close();
			statement2.close();
			connection.close();
			return true;
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		return false;
	}

	//returns null if level isn't the latest level
	public static String[] getDoneCategories(String user, String level)
	{	
		String [] categories = null;	
		Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);

			ResultSet categoriesDone = statement.executeQuery("SELECT categoriesDone, level FROM users where username = '"+user+"'");

			while(categoriesDone.next())
			{
				if(categoriesDone.getString("level").compareTo(level) != 0) 
				{
					closeConn(connection, true);
					return null;
				}
				categories = categoriesDone.getString("categoriesDone").split(",");
			}
			
			statement.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}

		return categories;
	}

	public static boolean isNewLevel(String user, String level)
	{
		boolean movedLevel = false;
		Connection connection = null;

		try
		{
			connection = DriverManager.getConnection("jdbc:sqlite:users.db");

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);
			Statement statement2 = connection.createStatement();
			statement2.setQueryTimeout(30);
			
			ResultSet userCate = statement2.executeQuery("SELECT categoriesDone FROM users WHERE  username = '"+user+"'");

			List<String> categories = Arrays.asList(userCate.getString(1).split(","));
			int size = 0;
			int totalSize = 0;
			ResultSet cateIds =  statement.executeQuery("SELECT id FROM categories WHERE level LIKE '%"+level+"%'");
			while(cateIds.next())
			{
				for(String a : categories)
				{
					if(cateIds.getString(1).compareTo(a) == 0)
						++size;
				}
				++totalSize;
			}

			if(totalSize == size)
			{
				level = Integer.toString(Integer.parseInt(level)+1);
				statement.executeUpdate("update users set level = '"+level+"' where username = '"+user+"'");
				statement.executeUpdate("update users set categoriesDone = '' where username = '"+user+"'");
				movedLevel = true;
			}
			statement2.close();
			statement.close();
			connection.close();
		}catch(SQLException e)
		{
			e.printStackTrace();
		}

		return movedLevel;
	}
}
